package cn.tmall.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.tmall.bean.Category;
import cn.tmall.bean.Property;
import cn.tmall.util.DBUtil;

public class PropertyDAO {
	Connection conn;
	ResultSet rs;
	PreparedStatement pstmt;
	Statement stmt;
	
	
	public void connetion(){
		try {
			conn = DBUtil.getConnetion();
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void add(Property bean){
		connetion();
		String sql="insert into property value(null,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,bean.getCategory().getId());
			pstmt.setString(2, bean.getName());
			pstmt.execute();
			rs = pstmt.getGeneratedKeys();
			while(rs.next()){
				bean.setId(rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	public void update(Property bean){
		connetion();
		String sql="update property set cid=?,name=? where id=?";
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bean.getCategory().getId());
			pstmt.setString(2, bean.getName());
			pstmt.setInt(3, bean.getId());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	
	public void delete(int id){
		connetion();
		String sql="delete from property where id="+id;
		try {
			stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public Property get(int id){
		connetion();
		Property p = null;
		String sql="select * from property where id="+id;
		try {
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				p = new Property();
				p.setId(id);
				p.setName(rs.getString("name"));
				Category category = new CategoryDAO().get(rs.getInt("cid"));
				p.setCategory(category);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return p;
	}
	
	public List<Property> list(int cid){
		return list(cid,0,Short.MAX_VALUE);
	}
	
	public List<Property> list(int cid,int start,int count){
		connetion();
		List<Property> list = new ArrayList<Property>();
		String sql="select * from property where cid=? order by id desc limit ?,?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cid);
			pstmt.setInt(2, start);
			pstmt.setInt(3, count);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Property property = new Property();
				property.setId(rs.getInt("id"));
				property.setName(rs.getString("name"));
				property.setCategory(new CategoryDAO().get(rs.getInt("cid")));
				
				list.add(property);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public int getTotal(int cid){
		connetion();
		int total = 0;
		String sql = "select count(*) from property where cid="+cid;
		try {
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeAll(rs, conn, pstmt, pstmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return total;
	}
	
}
